Group functions

Group functions are used to calculate the value of an expression from groups of related records.

Note: Group functions are retained for backward compatibility. If you know SQL, use Query APIs instead of group functions because all the operations that you can perform using group functions can also be performed using Query APIs.

Platform formulas support four types of group functions:

Function Result Parameters
#CALC_SUM.R8011457( expression | condition ) SUM of expressions for records where condition is true. Expression is mandatory and must be numeric. Condition is optional (default to true).
#CALC_COUNT.R8011457( expression | condition ) Counts all records for which expression is not null and condition is true. Expression is optional (default to 1) and must be numeric. Condition is optional (default to true).
#CALC_MAX.R8011457( expression | condition ) MAX of expressions for records where condition is true. Expression is mandatory and must be numeric. Condition is optional (default to true).
#CALC_MIN.R8011457( expression | condition ) MIN of expressions for records where condition is true. Expression is mandatory and must be numeric. Condition is optional (default to true).
Writers, the following contains important info that needs to be moved.

You can also run group functions on the entire set of object records. Use the object integration name instead of a specific relationship name, such as:

 #CALC_SUM.invoice( amount | true )

When writing expressions and conditions for group functions do not use tokens from the Select Merge Token box; rather, use the Group Token box for fields from related records.

Note: Inside a group function body, you must not use tokens in {! .. } format or equate hard-coded numeric or string values with the special tokens. For example, the TODAY token specifies the current time, but you cannot get yesterday’s time using (TODAY-1). This results in an error.

Whenever faced with a restriction using group functions, consider using the Query APIs as an alternative (see Query API).

Group functions use SQL syntax rather than JavaScript syntax for expressions and conditions. In simple cases you may not notice a difference. For Group Function conditions you can use the following special tokens:

  • TODAY for the current time
  • WEEK for 12PM of last Sunday
  • MONTH for 12PM of 1st day of the current month
  • QUARTER for 12PM of 1st day of the current quarter
  • YEAR for 12PM of 1st day of the current year
  • CURR_USER for id of the currently logged in user

You can also use integration codes from picklists and status fields. Examples of group functions:

  • Maximum value of amount field among related records created in the current quarter: 
    #CALC_MAX.R8011457( amount | createdAt>=QUARTER )
  • Count the number of related records where address1 field is not null: 
    #CALC_COUNT.R8011457( address1 )
  • Sum the amount field for all invoice records with a due_date after January 1st current year: 
    #CALC_SUM.invoice( amount | due_date>=YEAR );